SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [testing].[FSIC_SOI] AS
--***re-insert these lines

BEGIN

 
 DECLARE @FundID VARCHAR(10)
 SET @FundID = 'FSIC'
  SET FMTONLY ON;
        DECLARE @fmtonlyON BIT
        SET @fmtonlyON = 0
        IF ( 1 = 0 ) 
            BEGIN
                SET @fmtonlyON = 1
            END
        SET FMTONLY OFF;
           
           
        SET NOCOUNT ON;
 

 
 
 --FORMAT_OFF
-- declare @Proprietary Table(
--	InstrumentID int,
--	PortfolioID varchar(max),
--	PropReason varchar(Max), 
--	PropMarketVal int)

--insert into @Proprietary(
--		InstrumentID ,
--		Propreason,
--		PropMarketVal )
--select 
--		a.InstrumentId, 
--		a.PropReason,
--		case when a.propreason = '' then '0' else sum(a.MktVal) end

--		from Reporting.Asset a 
--		where a.ExportId = (SELECT MAX(ExportId)
--            FROM    FSFundModel.Reporting.Export
--            WHERE   fundid = @FUNDID )
		
--group by a.InstrumentId, a.PropReason

 
 DECLARE @Data TABLE(
      instrumentID INT ,
	  PortfolioID VARCHAR( MAX	),
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA DECIMAL(38, 10) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) , 
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
      Maturity Date ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) ,
      PIK DECIMAL(38, 10) ,
      RateType VARCHAR(MAX) ,
      BaseRateType VARCHAR(MAX) ,
      FixedRate DECIMAL(38, 10) ,
      Spread DECIMAL(38, 10) ,
      FLOOR DECIMAL(38, 10) ,
	  InstrClass Varchar(MAX), 
	  SortOrder INT,
	  QualAsset BIT,
	  CostDiscCurrPrc DECIMAL(38,10),
	  instTypeDescr VARCHAR( MAX	),
	  FinType VARCHAR(MAX ), 
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date,
	  AtCost VARCHAR(MAX),
	  Qualified VARCHAR(MAX),
	  LevTranche DECIMAL(38,10),
	  ExternalID VARCHAR(50)

    )
--FORMAT_ON
--FORMAT_OFF
 DECLARE @ConsolidatedData TABLE(
      instrumentID INT ,
	  PortfolioID VARCHAR( MAX	),
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA DECIMAL(38, 10) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) ,
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
      Maturity Date ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) ,
      PIK DECIMAL(38, 10) ,
      RateType VARCHAR(MAX) ,
      BaseRateType VARCHAR(MAX) ,
      FixedRate DECIMAL(38, 10) ,
      Spread DECIMAL(38, 10) ,
      FLOOR DECIMAL(38, 10) ,
	  InstrClass Varchar(MAX), 
	  SortOrder INT,
	  QualAsset BIT,
	  CostDiscCurrPrc DECIMAL(38,10),
	  instTypeDescr VARCHAR( MAX	),
	  FinType VARCHAR(MAX ),
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date ,
	  atcost VARCHAR(MAX),
	  Qualified VARCHAR(MAX),
	  LevTranche DECIMAL(38,10),
	  ExternalID VARCHAR(50)
    )
--FORMAT_ON
--FORMAT_OFF
DECLARE @Results TABLE (
	  InstrumentID INT,
      IssuerName VARCHAR(MAX) ,
      FootNotes VARCHAR(MAX) ,
      Industry VARCHAR(MAX) ,
      EBITDA varchar(MAX) ,
	  PropReason varchar(max) ,
	  PctProp DECIMAL(38, 10) ,
	  PricingSource varchar(max) ,
	  InvestmentRating int,
	  MoodysRating varchar(max) ,
	  BidPr DECIMAL(38, 10) ,
	  AskPr DECIMAL(38, 10) ,
	  TodaysPrice DECIMAL(38, 10) ,
	  Rate VARCHAR(MAX) ,
	  FLOOR VARCHAR(MAX	),
	  Maturity VARCHAR(MAX) ,
      PrincipalAmt DECIMAL(38, 10) ,
      AmortizedCost DECIMAL(38, 10) ,
      FairVal DECIMAL(38, 10) , 
	  sortOrder INT,
	  InstrType VARCHAR(MAX),
	  IncomeAtCurrentPrice DECIMAL(38, 10),
	  OriginalCost DECIMAL(38, 10),
	  shares DECIMAL(38, 10),
	  OriginalPurchaseDate Date,
	  atcost VARCHAR(MAX),
	  Qualified VARCHAR(MAX),
	  LevTranche DECIMAL(38,10),
	  ExternalID VARCHAR(50)

	  )
 INSERT INTO @Data
             (
                         instrumentID
						, PortfolioID
                       , IssuerName
                       , FootNotes
                       , Industry
                       , EBITDA
					   , PropReason
					   , PctProp
					   , PricingSource
					   , InvestmentRating
					   , MoodysRating
					   , BidPr
					   , AskPr
					   , TodaysPrice
					   , Rate
                       , Maturity
                       , PrincipalAmt
                       , AmortizedCost
                       , FairVal
                       , PIK
                       , RateType
                       , BaseRateType
                       , FixedRate
                       , Spread
                       , FLOOR
                       , InstrClass
						, SortOrder        
						, QualAsset        
						, CostDiscCurrPrc
						, instTypeDescr
						, FinType
						, IncomeAtCurrentPrice 
						, OriginalCost
						, shares
						, OriginalPurchaseDate
						, AtCost
						, Qualified
						, LevTranche
						, ExternalID
				
             )
 
SELECT   i.InstrumentId
		, a.PortfolioId
        , iss.IssuerName
        , NULL
        , Iss.SubSector
        , Iss.TTMEBITDA
		, actsum.PropReason
		, case when actsum.PropReason <> '' then a.mktval else '0' end
		, i.PricingVendor
		, a.AssQualRat
		, i.MoodysRating
		, pr.Bid
		, pr.Offer
		, a.TodayPrice
		, NULL
		, i.MaturityDate 
		, a.EndAdjAmt
		, a.AmortCost
		, a.MktVal
		, i.PIK
		, i.RateType
		, i.BaseRateType
		, i.FixedRate
		, i.Spread
		, i.Floor 
		, id.InstrClass    
		, id.SortOrder
		, fi.IsQualified
		, a.PctCostDiscCurrPrc
		, id.InstTypeDescr
		, id.FinType
		, a.TotalIntCurrPurchPrc
		, a.Cost
		, a.Shares
		, a.PurchDate
		, i.PricingMethodType
		, NULL
		, i.FaceLevThruTrn      
		, i.ExtId
 

 
 FROM       FSFundModel.Reporting.Asset a
            INNER JOIN FSFundModel.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            INNER JOIN FSFundModel.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            INNER JOIN FSFundModel.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
            INNER JOIN FSFundModel.Reporting.FundIssuer fi
            ON         a.ExportId  = fi.ExportId
            AND        fi.IssuerId = iss.IssuerId
            INNER JOIN FSFundModel.Reporting.Portfolio p
            ON         a.ExportId    = p.ExportId
            AND        a.PortfolioId = p.PortfolioId
            INNER JOIN FSFundModel.Reporting.Fund f
            ON         a.ExportId = f.ExportId
			INNER JOIN  FSFundModel.Reporting.Trade t ON t.ExportId = a.ExportId
			AND t.LotId =a.LotId
			AND t.InstrumentId = a.InstrumentId
			AND t.PortfolioId = a.PortfolioId

			INNER JOIN  FSFundModel.Reporting.ActivitySummary actsum
			on actsum.ExportId = a.ExportId
			and actsum.ActivitySummaryId = t.ActivitySummaryId
			LEFT JOIN FSFundModel.Reporting.Pricing Pr
			ON			a.ExportId = pr.ExportId
			AND			cast(f.CurrNavDate as date) = pr.PriceDate
			AND			i.ExtId = pr.PricingId
			--INNER JOIN  @Proprietary prop
			--ON			a.InstrumentId = prop.InstrumentID
			--AND			i.InstrumentId = prop.InstrumentID
			
			
            --INNER JOIN FSFundModel.Reporting.Trade t
            --ON         a.ExportId     = t.ExportId
            --AND        i.InstrumentId = t.InstrumentId
            --AND        a.LotId        = t.LotId
            --AND        a.PortfolioId  = t.PortfolioId
            --AND        a.InstrumentId = t.InstrumentId

 WHERE      a.ExportId =
            (SELECT MAX(ExportId)
            FROM    FSFundModel.Reporting.Export
            WHERE   fundid = @FundID )
		AND a.IsActiveEndOfDay =1



Update @Data
 SET FootNotes = COALESCE(FootNotes + '(ASF)', '(ASF)') WHERE LTRIM(RTRIM(PortfolioID ))= 'ARCH'
 Update @Data
 SET FootNotes = COALESCE(FootNotes + '(BSF)', '(BSF)') WHERE LTRIM(RTRIM(PortfolioID ))= 'BROAD'
 UPDATE @Data 
 SET FootNotes = COALESCE(FootNotes + '(LSF)', '(LSF)') WHERE PortfolioID = 'LOCUS'
 UPDATE @Data  
 SET FootNotes = COALESCE(FootNotes + '(RSF)', '(RSF)') WHERE LTRIM(RTRIM(PortfolioID ))= 'RACES'
  Update @Data
 SET FootNotes = COALESCE(FootNotes + '(WSF)', '(WSF)') WHERE LTRIM(RTRIM(PortfolioID ))= 'WALN'


  INSERT INTO @ConsolidatedData
             (
                         instrumentID
					   , PortfolioID
                       , IssuerName
                       , FootNotes
                       , Industry
                       , EBITDA
					   , PropReason
					   , PctProp 
					   , PricingSource
					   , InvestmentRating
					   , MoodysRating
					   , BidPr
					   , AskPr
					   , TodaysPrice
					   , Rate
                       , Maturity
                       , PrincipalAmt
                       , AmortizedCost
                       , FairVal
                       , PIK
                       , RateType
                       , BaseRateType
                       , FixedRate
                       , Spread
                       , FLOOR
                       , InstrClass
					   , SortOrder        
						, QualAsset         
						, CostDiscCurrPrc
						, instTypeDescr
						, FinType 
						, IncomeAtCurrentPrice
						, OriginalCost
						, shares
						, OriginalPurchaseDate
						, atcost 
						, Qualified
						, LevTranche
						, ExternalID
             )


 SELECT     d.InstrumentId
		, MAX(d.PortfolioId)
        , MAX(d.IssuerName)
        ,  NULL
        , MAX(d.industry)
        , MAX(d.EBITDA)
		, MAX(d.propreason)
		, SUM(d.pctprop) 
		, MAX(d.PricingSource)
		, max(d.investmentrating)	
        , max(d.moodysrating)
		, max(d.bidpr)
		, max(d.askpr)
		, MAX(d.todaysprice)
		,  NULL
		, MAX(d.Maturity)
		, SUM(d.PrincipalAmt)
		, SUM(d.AmortizedCost)
		, SUM(d.FairVal)
		, MAX(d.PIK)
		, max(d.RateType)
		, max(d.BaseRateType)
		, max(d.FixedRate)
		, max(d.Spread)
		, max(d.Floor )
		, max(d.InstrClass)
		, max(d.SortOrder)
		, d.QualAsset
		, MAX(d.CostDiscCurrPrc)
		, MAX(d.instTypeDescr)
		, MAX(d.FinType)
		, sum(d.IncomeAtCurrentPrice)
		, sum(d.originalcost)
		, SUM(d.shares)
		, min(d.OriginalPurchaseDate)
		, MAX(d.atcost)
		, NULL
		, MAX(d.LevTranche)    
		, MAX(d.ExternalID)  

 

FROM @Data d


GROUP BY d.instrumentId , d.QualAsset

 
 
 INSERT INTO @Results
         ( InstrumentID ,
           IssuerName ,
           Industry ,   
		   EBITDA ,
		   PropReason ,
		   PctProp ,
		   PricingSource ,
		   InvestmentRating ,
		   MoodysRating ,
		   BidPr ,
		   AskPr , 
		   TodaysPrice ,
		   FootNotes ,                
           PrincipalAmt ,
           AmortizedCost ,
           FairVal ,
		   sortOrder ,
		   InstrType ,
		   IncomeAtCurrentPrice,
		   OriginalCost ,
		   shares ,
		   OriginalPurchaseDate,
		   atcost,

		   LevTranche,
		   ExternalID
         )

 SELECT 
	DISTINCT d.InstrumentID,
	d.IssuerName, 
	d.Industry,  
	d.EBITDA,
	d.PropReason ,
	case when d.fairval = '0' then '0' else d.pctprop / d.FairVal end,
	d.PricingSource,
	d.InvestmentRating ,
	d.MoodysRating ,
	d.Bidpr,
	d.askpr,
	d.TodaysPrice ,
	STUFF((
			SELECT '' + tmp.FootNotes FROM (SELECT 
			 DISTINCT d.instrumentID,d.IssuerName, d.FootNotes
			from 
				@Data d ) AS tmp
			WHERE tmp.instrumentid = d.instrumentID
				FOR XML PATH ('')
				), 1,0, ''),
				 d.PrincipalAmt ,
      d.AmortizedCost  ,
      d.FairVal  ,
	  d.sortorder,
	  d.instrClass ,
	  d.IncomeAtCurrentPrice,
	  d.OriginalCost ,
	  d.shares ,
	  d.OriginalPurchaseDate,
	  d.atcost,
	  d.LevTranche,
	  d.ExternalID

 FROM	@ConsolidatedData d
 
 ORDER BY d.sortOrder ASC, d.issuerName ASC , d.instrumentid 

  Update  r
  SET r.FootNotes = COALESCE(r.FootNotes + '(Non-Qualified)', '(Non-Qualified)') 
 FROM @Results r
 INNER JOIN @ConsolidatedData d ON r.IssuerName= d.issuerName 
  WHERE d.QualAsset = 0
  
  Update  r
  SET r.Qualified = COALESCE(r.Qualified + 'Non-Qualified', 'Non-Qualified') 
	FROM @Results r
	INNER JOIN @ConsolidatedData d ON r.IssuerName= d.issuerName 
	 WHERE d.QualAsset = 0
  
  
  UPDATE r
  SET  r.Rate = CASE WHEN d.RateType LIKE '%Floating%' THEN
				CASE WHEN d.BaseRateType LIKE '%LIBOR%' THEN 'L+' + CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
				     when d.BaseRateType LIKE '%EURIBOR%' THEN 'EURIBOR+' +  CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
					 when d.BaseRateType LIKE '%PRIME%' THEN 'Prime+' +  CAST (CAST	((d.spread*10000) AS DECIMAL(38,0)) AS varchar (MAX))
				END 
			END 
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId

UPDATE r
SET r.Rate  = CASE WHEN d.rateType = 'Fixed' THEN CAST (CAST	((d.fixedRate*100) AS DECIMAL(38,1)) AS varchar (MAX)) + '%'
				ELSE r.Rate
			END	
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId



UPDATE r --%O - % matches for Collateralized Secs 
SET r.Rate  = CASE WHEN d.rateType = 'None' THEN 
				case when d.instTypeDescr LIKE '%O - %' then CAST (CAST	((d.CostDiscCurrPrc *100) AS DECIMAL(38,1)) AS varchar (MAX)) + '%'
				END 
				ELSE r.Rate
			END	
FROM @Results r 
INNER JOIN  @ConsolidatedData d ON r.InstrumentID = d.instrumentID

UPDATE r
SET r.FLOOR = CASE WHEN d.RateType LIKE '%Floor%' THEN 
				CASE WHEN d.BaseRateType LIKE '%LIBOR%' THEN CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% LIBOR Floor'
				WHEN d.baseRateType LIKE '%EURIBOR%' then CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% EURIBOR Floor'
			    WHEN d.baseRateType LIKE '%PRIME%' then CAST ( CAST ((d.FLOOR * 100 ) AS DECIMAL (38,1) ) AS varchar(MAX)) + '% Prime Floor'
				END 
			End
FROM @Results r 
INNER JOIN @ConsolidatedData d ON r.instrumentid = d.instrumentid


UPDATE r 
SET r.maturity = cast(( cast (datepart(month, d.Maturity) AS varchar(MAX)) + '/' + CAST( DATEPART(DAY, d.Maturity) AS varchar(MAX)) + 	
			'/' + right(CAST( DATEPART(year, d.Maturity) AS varchar(MAX)) , 2) ) AS varchar (MAX))
FROM @Results r
INNER JOIN @ConsolidatedData d ON R.InstrumentID = d.instrumentID

 SELECT r.* FROM @Results r 
		WHERE r.instrumentid NOT IN (
		SELECT fci.instrumentID FROM FSFundModel.Reporting.FundCreditInstrument fci
		WHERE 
		fci.ExportId = (SELECT MAX(e.ExportId) FROM fsfundmodel.Reporting.Export e WHERE e.FundId ='FSEPF') and fci.Drawn = '0')
END


GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Testing', 'SCHEMA', N'testing', 'PROCEDURE', N'FSIC_SOI', NULL, NULL
GO
